﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Diagnostics;

/// <summary>
/// user的數據訪問類：增刪改查等操作
/// </summary>
namespace T01WPF
{
    class UserDao
    {
        //MySQL的連接串描述
        private static string connStr
        = "server=localhost;user=root;password=;database=gametest";
        /// <summary>
        /// 登錄檢查
        /// </summary>
        /// <param name="username">用戶名</param>
        /// <param name="password">密碼</param>
        /// <returns>0：無效 1：登錄成功</returns>
        public static int LoginCheck(string username,string password)
        {
            //默認無效的用戶名和密碼
            int number = 0;
            //構建MySQL連接對象
            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                //打開數據庫鏈接
                conn.Open();
                //比較用戶名與密碼的sql語句
                string sql = "SELECT COUNT(id) FROM `tb_users` WHERE `username`='"
                    + username + "' AND `password`='" + password + "'";
                Debug.WriteLine("SQL語句:" + sql);
                //創建SQL命令
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //執行SQL命令
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    number = Convert.ToInt32(result);
                }
            }
            catch (Exception ex) {
                Debug.WriteLine("數據庫訪問異常...");
                Debug.WriteLine("======================");
                Debug.WriteLine(ex.ToString());
                Debug.WriteLine("======================");
            }
            finally{
                if (conn !=null ){
                    //關閉數據庫鏈接
                    conn.Close();
                }
            }
           return number;
        }

        /// <summary>
        /// 獲取所有用戶列表
        /// </summary>
        /// <returns>用戶列表結合</returns>
        public static List<User> LoadData()
        {
            //构建用戶集合
            List<User> userList = new List<User>();

            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                conn.Open();
                string sql = "SELECT * FROM tb_users";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //獲取數據訪問的游標(當前記錄）
                MySqlDataReader rdr = cmd.ExecuteReader();
                //依次訪問數據記錄並加入數據集合
                while (rdr.Read()){
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                    user.Username = Convert.ToString(rdr["username"]);
                    user.Password = Convert.ToString(rdr["password"]);
                    user.Nickname = Convert.ToString(rdr["nickname"]);
                    user.IsAdmin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false ;
                    userList.Add(user);
                }
                //關閉游標
                rdr.Close();
            }
            catch (Exception ex )
            {
                Debug.WriteLine(ex.ToString());
            }finally{
                if (conn != null)
                    conn.Close();
            }
            //return new NotFiniteNumberException();
            return userList;
        }

        /// <summary>
        /// 按条件获取用户列表
        /// </summary>
        /// <param name="condition">条件参数值</param>
        /// <returns>符合条件的用户集合</returns>
        public static List<User> LoadData(string condition){
            //构建用戶集合
            List<User> userList = new List<User>();

            MySqlConnection conn = new MySqlConnection(connStr);
            try{
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM tb_users WHERE username LIKE @username OR nickname LIKE @nickname";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数（占位符）
                cmd.Parameters.AddWithValue("@username",
                    "%" + condition + "%");
                MySqlDataReader rdr = cmd.ExecuteReader();
                cmd.Parameters.AddWithValue("@nickname",
                    "%" + condition + "%");
                while (rdr.Read())
                {
                    User user = new User();
                    user.Id = Convert.ToInt64(rdr["id"]);
                            user.Id = Convert.ToInt64(rdr["id"]);
                            user.Username = Convert.ToString(rdr["username"]);
                            user.Password = Convert.ToString(rdr["password"]);
                            user.Nickname = Convert.ToString(rdr["nickname"]);
                            user.IsAdmin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;
                            userList.Add(user);
                        }
                rdr.Close();

            }catch (Exception e){
                Debug.WriteLine(e.ToString());
            }finally{
                if (conn != null){
                    conn.Close();
                }
            }
            Debug.WriteLine(userList.Count);
            return userList;
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="user">用户数据实体</param>
        /// <returns>MySQL自增ID</returns>
        public static long Insert(User user)
        {
            long insertId = 0;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
             {
                conn.Open();
                string sql = "INSERT INTO tb_users "
                    + "(username,password,nickname,is_admin) "
                    + "VALUES "
                    + "(@username,@password,@nickname,@is_admin) ";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //参数绑定
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);
                cmd.ExecuteNonQuery();
                insertId = cmd.LastInsertedId;
            }catch (Exception ex){
                Debug.WriteLine("数据插入异常: " + ex.ToString());
            }
            finally{
                if(conn != null)
                {
                    conn.Close();
                }
            }
            return insertId;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">用户数据实体</param>
        public static void Update(User user)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                string sql = "UPDATE tb_users SET "
                    + "username=@username, "
                    + "password=@password, "
                    + "nickname=@nickname, "
                    + "is_admin=@is_admin "
                    +"WHERE id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //参数绑定
                cmd.Parameters.AddWithValue("@username", user.Username);
                cmd.Parameters.AddWithValue("@password", user.Password);
                cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);
                cmd.Parameters.AddWithValue("@id", user.Id);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("数据更新异常: " + ex.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            
        }

        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="id">删除用户的内部id</param>
        public static void Delete(long id){
            MySqlConnection conn = new MySqlConnection(connStr);
            try{
                conn.Open();
                string sql = "DELETE FROM tb_users WHERE id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }catch (Exception ex ){
                Debug.WriteLine("数据删除错误" + ex.ToString());

            }finally{
                if(conn != null){

                }
            }
        }
    }
}
